import pymysql

from model.Currm import Currm
from model.Student import Student
from model.StudentCurrm import StudentCurrm


class StudentService:
    def create_student(self, inp):
        sqlConnect = pymysql.Connect(
            host='localhost',
            port=3306,
            user='root',
            passwd='123456',
            db='manager',
            charset='utf8'
        )
        cursor = sqlConnect.cursor()
        idSql = "SELECT * FROM generate"
        generateUpdate = "update generate set num=%s"
        try:
            print(idSql)
            cursor.execute(idSql)
            studentId = cursor.fetchone()[0]
            print(generateUpdate % (studentId + 1))
            cursor.execute(generateUpdate % (studentId + 1))
            inp['id'] = studentId
        except Exception as e:
            sqlConnect.rollback()
            return 'sql执行错误' + e.__str__()

        sql = "INSERT INTO STUDENT (id, number, name, sex, department, address, classes) VALUES ('%s', '%s', '%s','%s','%s', '%s','%s')"
        data = Student.map_to_data(inp)
        try:
            print(sql % data)
            cursor.execute(sql % data)
        except Exception as e:
            sqlConnect.rollback()
            return 'sql执行错误' + e.__str__()
        else:
            sqlConnect.commit()
            cursor.close()
            sqlConnect.close()
            return 'SUCCESS'

    def getById(self, id):
        sqlConnect = pymysql.Connect(
            host='localhost',
            port=3306,
            user='root',
            passwd='123456',
            db='manager',
            charset='utf8'
        )
        cursor = sqlConnect.cursor()
        sql = "SELECT * FROM STUDENT WHERE id = %s"
        print(sql % id)
        cursor.execute(sql % id)
        try:
            result = cursor.fetchone()
            entity = Student.result_to_entity(result)
        except:
            return '未找到id'
        cursor.close()
        sqlConnect.close()
        return entity

    def list(self):
        sqlConnect = pymysql.Connect(
            host='localhost',
            port=3306,
            user='root',
            passwd='123456',
            db='manager',
            charset='utf8'
        )
        cursor = sqlConnect.cursor()
        sql = "SELECT * FROM STUDENT"
        print(sql)
        cursor.execute(sql)
        try:
            result = cursor.fetchall()
            studentList = list(map(Student.result_to_entity_dict, result))
        except:
            return 'sql错误'
        cursor.close()
        sqlConnect.close()
        return studentList

    def delete(self, student):
        sqlConnect = pymysql.Connect(
            host='localhost',
            port=3306,
            user='root',
            passwd='123456',
            db='manager',
            charset='utf8'
        )
        cursor = sqlConnect.cursor()
        sql = "DELETE FROM STUDENT WHERE id = %s"
        sql = sql % student.id
        print(sql)
        try:
            cursor.execute(sql)
        except:
            return 'sql错误'
        sqlConnect.commit()
        cursor.close()
        sqlConnect.close()
        return True

    def update(self, inp, studentId):
        sqlConnect = pymysql.Connect(
            host='localhost',
            port=3306,
            user='root',
            passwd='123456',
            db='manager',
            charset='utf8'
        )
        cursor = sqlConnect.cursor()
        sql = "UPDATE STUDENT SET number='%s', name='%s', sex='%s', department='%s',address='%s',classes='%s' WHERE id = %s"
        data = (inp['number'], inp['name'],inp['sex'], inp['department'], inp['address'], inp['classes'], studentId)
        try:
            print(sql % data)
            cursor.execute(sql % data)
        except Exception as e:
            sqlConnect.rollback()
            return 'sql执行错误' + e.__str__()
        else:
            sqlConnect.commit()
            cursor.close()
            sqlConnect.close()
            return 'SUCCESS'

    def selected_currms(self,studentId):
        sqlConnect = pymysql.Connect(
            host='localhost',
            port=3306,
            user='root',
            passwd='123456',
            db='manager',
            charset='utf8'
        )
        cursor = sqlConnect.cursor()
        sql = "select currm.name,currm.teacher_name,score,currm.id from currm,student_currm where student_currm.student_id = %s and currm.id = student_currm.currm_id"
        sql = sql % studentId
        print(sql)
        cursor.execute(sql)
        try:
            result = cursor.fetchall()
            studentList = list(map(StudentCurrm.result_to_entity_dict, result))
        except:
            return 'sql错误'
        cursor.close()
        sqlConnect.close()
        return studentList

    def selectable_currms(self,studentId):
        sqlConnect = pymysql.Connect(
            host='localhost',
            port=3306,
            user='root',
            passwd='123456',
            db='manager',
            charset='utf8'
        )
        cursor = sqlConnect.cursor()
        sql = "select * from currm where not exists (select * from student_currm where currm_id = currm.id and student_id = %s)"
        sql = sql % studentId
        print(sql)
        cursor.execute(sql)
        try:
            result = cursor.fetchall()
            studentList = list(map(Currm.result_to_entity_dict, result))
        except:
            return 'sql错误'
        cursor.close()
        sqlConnect.close()
        return studentList
